/**

	EXEC SP_BOOKINGS_BILLINGS_REPORT_PROC 12
*/
ALTER  PROC SP_BOOKINGS_BILLINGS_REPORT_PROC @NO_OF_MONTHS INT
 AS
 BEGIN
	DECLARE @RESULT_TABLE TABLE (		
			MONTH_YEAR VARCHAR(20),
			MONTH INT,
			YEAR INT,
			BOOKING_QTY	INT,
			BOOKING_PRICE FLOAT,
			BOOKING_AMOUNT NUMERIC(10,3),
			BILLING_QTY INT,
			BILLING_PRICE FLOAT,
			BILLING_AMOUNT NUMERIC(10,3)
			)

	DECLARE @MONTH_COUNT 	INT
	DECLARE @MONTH 		INT
	DECLARE @YEAR 		INT
	DECLARE @BILLING_QTY INT
	DECLARE @BILLING_PRICE FLOAT
	DECLARE @BOOKING_QTY INT
	DECLARE @BOOKING_PRICE FLOAT
	DECLARE @START_DATE DATETIME
	DECLARE @END_DATE DATETIME
	DECLARE @SHOW_NEXT INT
	DECLARE @SHORT_MONTH_NAME VARCHAR(10)

	SET @MONTH	= MONTH(GETDATE())-1 --zero based
	SET @YEAR	= YEAR(GETDATE())

	SET @MONTH_COUNT= 0
	SET @SHOW_NEXT = @NO_OF_MONTHS
	WHILE ( @MONTH_COUNT < @SHOW_NEXT )
	 BEGIN
		IF ( @MONTH < 0 ) --going backward
		 BEGIN
			SET @MONTH = 11
			SET @YEAR  = @YEAR - 1
		 END
		SET @START_DATE = CONVERT(DATETIME, CONVERT( VARCHAR, @MONTH+1 )+'/01/'+ CONVERT( VARCHAR, @YEAR ))
		SET @END_DATE	= DATEADD( mi, -1 , DATEADD( m, 1, CONVERT(DATETIME, @START_DATE ) ) )

		PRINT @START_DATE
		PRINT @END_DATE

		SET @BILLING_QTY = 0
		SET @BILLING_PRICE = 0
		SET @BOOKING_QTY = 0
		SET @BOOKING_PRICE = 0

		SELECT @BILLING_QTY = ISNULL(SUM(CSLIT_Qty_SHIPPED),0), @BILLING_PRICE = ISNULL(AVG(COOL_UNIT_COST),0) FROM Customer_Shipping_Line_Item_Table CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL
			WHERE CSLIT.LineItem_ID = COOL.LineItem_ID
				AND CSLIT_SHIPPED_DATE >= @START_DATE AND  CSLIT_SHIPPED_DATE <= @END_DATE
				AND CSLIT.CSLIT_STATUS = 'SHIPPED'
		
		/*SET @BILLING_PRICE = (SELECT AVG(COOL_UNIT_COST) FROM Customer_Shipping_Line_Item_Table CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO
			WHERE CSLIT.LineItem_ID = COOL.LineItem_ID AND COOL.SALESORDER_ID = SO.SALESORDER_ID 
				AND CSLIT_SHIPPED_DATE >= @START_DATE AND  CSLIT_SHIPPED_DATE <= @END_DATE
				AND CSLIT.CSLIT_STATUS = 'SHIPPED')*/
		
		SELECT @BOOKING_QTY = ISNULL(SUM(COOL_QTY),0), @BOOKING_PRICE = ISNULL(AVG(COOL_UNIT_COST),0) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL
				WHERE COOL_BOOKING_DATE >= @START_DATE 
					AND COOL_BOOKING_DATE <= @END_DATE

		
		SET @SHORT_MONTH_NAME = left( datename( month,@START_DATE), 3 )

		INSERT INTO @RESULT_TABLE (
								MONTH_YEAR,
								MONTH,
								YEAR,
								BOOKING_QTY,
								BOOKING_PRICE,
								BOOKING_AMOUNT,
								BILLING_QTY,
								BILLING_PRICE,
								BILLING_AMOUNT)
			VALUES ( @SHORT_MONTH_NAME + '-' +  convert(varchar,@YEAR),
					@MONTH,
					@YEAR,
					@BOOKING_QTY,
					@BOOKING_PRICE,
					(@BOOKING_QTY*@BOOKING_PRICE*0.1),
					@BILLING_QTY,
					@BILLING_PRICE,
					(@BILLING_QTY*@BILLING_PRICE*0.1))

		SET @MONTH = @MONTH - 1
		SET @MONTH_COUNT = @MONTH_COUNT + 1
	 END

	SELECT * FROM @RESULT_TABLE	ORDER BY YEAR, MONTH
 END
